package org.example.shoppingmall.dao;

import org.example.shoppingmall.entity.Cart;
import org.example.shoppingmall.entity.Category;
import org.example.shoppingmall.entity.Goods;
import org.example.shoppingmall.util.HikariUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CartDao {
    /**
     * 删除购物车里的已选商品
     * @param goods_id
     * @return
     */
    public boolean goodsDelete(Integer goods_id,Integer user_id){
        String sql = "DELETE FROM tb_cart WHERE goods_id = ? AND user_id = ?";
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,goods_id);
            ps.setInt(2,user_id);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 修改购物车内某商品购买数量
     * @param goods_id
     * @param quantity
     * @return
     */
    public boolean cartUpdate(Integer goods_id,Integer quantity,Integer user_id){
        String sql = "UPDATE tb_cart SET quantity = ? WHERE goods_id = ? AND user_id = ?";
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,quantity);
            ps.setInt(2,goods_id);
            ps.setInt(3,user_id);
            int rows = ps.executeUpdate();
            return rows > 0;
        }catch (SQLException e){
            e.printStackTrace();

        }
        return false;
    }

    /**
     * 获取购物车内商品列表并返回
     * @param page_size
     * @param page
     * @return
     */
    public List<Goods> goodsSearchInCart(Integer page_size, Integer page,Integer user_id){
        String sql = "SELECT g.*,c.* FROM tb_cart c join tb_goods g ON c.goods_id = g.id WHERE c.user_id = ? limit ?,?";
        List<Goods> list = new ArrayList<>();
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,user_id);
            ps.setInt(2,page_size*(page-1));
            ps.setInt(3,page_size);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Goods goods = new Goods();
//                    goods.setId(rs.getInt("id"));
//                    goods.setCategory_id(rs.getInt("category_id"));
                    goods.setName(rs.getString("name"));
                    goods.setPicture(rs.getString("picture"));
                    goods.setPrice(rs.getBigDecimal("price"));
                    goods.setQuantity(rs.getInt("quantity"));
//                    goods.setResume(rs.getString("resume"));
//                    goods.setStock(rs.getInt("stock"));
//                    goods.setUser_id(rs.getInt("user_id"));
                    list.add(goods);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 记录购物车内有多少条商品数据
     * @param
     * @param
     * @return
     */
    public Integer goodsCountInCart(Integer user_id){
        String sql = "SELECT * FROM tb_cart WHERE user_id = ?";
        int count = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,user_id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    count++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 选取购物车内要买的商品
     * @param goods_id
     * @return
     */
    public Cart getInfoFromCart(Integer goods_id){
        String sql = "SELECT * FROM tb_cart WHERE goods_id = ?";

        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, goods_id);
            try(ResultSet rs = ps.executeQuery()){
                if (rs.next()) {
                    Cart cart = new Cart();
                    cart.setUser_id(rs.getInt("user_id"));
                    cart.setGoods_id(rs.getInt("goods_id"));
                    cart.setQuantity(rs.getInt("quantity"));
                    return cart;
                }
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
       return null;
    }

    /**
     * 将购物车结算成订单(二重判断之后)
     * @param address
     * @param phone
     * @return
     */
    public Integer settleIntoOrder(String address,String phone,Cart cart){
        String sql = "INSERT INTO tb_order (address,phone,user_id,goods_id,quantity,status) VALUES (?,?,?,?,?,?)";
        Integer rows = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1, address);
            ps.setString(2, phone);
            ps.setInt(3, cart.getUser_id());
            ps.setInt(4,cart.getGoods_id());
            ps.setInt(5,cart.getQuantity());
            ps.setInt(6,0);
           rows = ps.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();

        }
        return rows;
    }

    /**
     * 结算账户余额,修改数据库(二重判断之后)
     * @param id
     * @param remainder
     * @return
     */
    public boolean reduceBalance(Integer id,BigDecimal remainder){
        String sql = "UPDATE tb_user SET balance = ? WHERE id = ?";
        int rows = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1,remainder);
            ps.setInt(2,id);
            rows = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return rows > 0;
    }

    /**
     * 修改商品库存,修改数据库(二重判断之后)
     * @param goods_id
     * @param stock
     * @return
     */
    public Integer updateStock(Integer goods_id,Integer stock){
        String sql = "UPDATE tb_goods SET stock = ? WHERE id = ?";
        int rows = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,stock);
            ps.setInt(2,goods_id);
            rows = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return rows;
    }

    /**
     * 通过商品id,用户id字段获取应付款
     * @param goods_id
     * @return
     */
    public BigDecimal accountsReceviable(Integer goods_id,Integer user_id) {
        String sql = "SELECT * FROM tb_cart c join tb_goods g ON c.goods_id = g.id WHERE c.goods_id = ? AND c.user_id = ?";
        BigDecimal amount = new BigDecimal("0.0");
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, goods_id);
            ps.setInt(2, user_id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    BigDecimal price = rs.getBigDecimal("price");
                    System.out.println("price:" + price);
                    Integer quantity = rs.getInt("quantity");
                    System.out.println("quantity:" + quantity);
                    amount = amount.add(price.multiply(new BigDecimal(quantity)));
                    System.out.println("amount:" + amount);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return amount;
    }

    /**
     * 通过用户id字段获取账户余额
     * @param user_id
     * @return
     */
    public BigDecimal getBalance(Integer user_id){
        String sql = "SELECT * FROM tb_user WHERE id = ?";
        BigDecimal balance = new BigDecimal("0.0");
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,user_id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    balance =  rs.getBigDecimal("balance");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return balance;
    }

    /**
     * 通过商品id字段获取商品库存
     * @param goods_id
     * @return
     */
    public Integer getStock(Integer goods_id){
        String sql = "SELECT * FROM tb_goods WHERE id = ?";
        Integer stock = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,goods_id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    stock =  rs.getInt("stock");
                    System.out.println("stock:" + stock);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println("stock:"+stock);
        return stock;
    }

    /**
     * 通过商品id字段获取购买数量
     * @param goods_id
     * @return
     */
    public Integer getQuantity(Integer goods_id,Integer user_id){
        String sql = "SELECT quantity FROM tb_cart WHERE goods_id = ? AND user_id = ?";
        Integer quantity = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,goods_id);
            ps.setInt(2,user_id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    quantity =  rs.getInt("quantity");
                    System.out.println("quantity:" + quantity);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println("quantity"+quantity);
        return quantity;
    }
}
